using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using Scb.Framework;
using Robot.Business;
using Robot.Data;
using Robot.Entities;
using System.Xml;
using System.IO;

namespace WebRobot.App_code
{
  public class InternalRate
  {
    #region Import method

    public int ImportInternalRate(string sFilePath, string sImportPeople, DateTime importDate)
    {
      string fileName = Path.GetFileName(sFilePath);// sFilePath.Split('\\')[sFilePath.Split('\\').Length - 1];
      try
      {
        tblInternalRateCollection intRateCol = new tblInternalRateCollection();

        DataTable tb = new DataTable();

        //get datatable from sheet "Internal Rate"
        tb = GetDataFromXLS(sFilePath, "Internal rate", false);

        intRateCol = ReadSheetInternalRate(tb, fileName, sImportPeople, importDate);

        tb = GetDataFromXLS(sFilePath, "Board parameter", false);

        intRateCol = ReadSheetBoardParameter(tb, intRateCol);
        if (intRateCol.Count > 0)
        {
          tblInternalRateService.DeletetblInternalRate(sImportPeople);
          foreach (tblInternalRate inRate in intRateCol)
          {
            tblInternalRateService.CreatetblInternalRate(inRate);
          }
        }
        return intRateCol.Count;
      }
      catch (Exception ex)
      {

        Logger.Error("WebRobot.App_code.InternalRate::ImportInternalRate", ex);
        return 0;
      }
    }

    #endregion

    #region Helper

    private bool IsRateRow(DataRow r)
    {
      try
      {
        decimal test;
        test = decimal.Parse(r.ItemArray[2].ToString());
        return true;
      }
      catch (Exception ex)
      {
        return false;
      }
    }

    private static DataTable GetDataFromXLS(string path, string sheet, bool isHeader)
    {

      sheet = sheet + "$";
      string strConnectionString = string.Empty;
      OleDbConnection cnCSV = new OleDbConnection(strConnectionString);
      OleDbDataAdapter daCSV = new OleDbDataAdapter();
      try
      {


        if (isHeader)
          strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
          "Data Source=" + path + ";" +
          "Extended Properties=" + (char)34 + "Excel 8.0;HDR=Yes;IMEX=1;" + (char)34;
        else
          strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
          "Data Source=" + path + ";" +
          "Extended Properties=" + (char)34 + "Excel 8.0;HDR=No;IMEX=1;" + (char)34;
        cnCSV = new OleDbConnection(strConnectionString);

        cnCSV.Open();
        OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [" + sheet + "]", cnCSV);

        daCSV.SelectCommand = cmdSelect;
        DataTable dtCSV = new DataTable();
        daCSV.Fill(dtCSV);
        cnCSV.Close();
        daCSV = null;
        return dtCSV;
      }
      catch (Exception ex)
      {
        Logger.Error("WebRobot.App_code.ExcRate::GetDataFromXLS", ex);
        cnCSV.Close();
        daCSV = null;
        throw;
      }
    }

    private tblInternalRateCollection ReadSheetInternalRate(DataTable tb, string fromFileName, string sImportPeople, DateTime importDate)
    {
      tblInternalRateCollection intRateCol = new tblInternalRateCollection();
      Guid fileID = new Guid();
      fileID = tblFileUploadService.GettblFileUploadByFileName(fromFileName, sImportPeople).ID;
      for (int i = 0; i < tb.Rows.Count; i++)
      {
        DataRow r = tb.Rows[i];
        if (IsRateRow(r))
        {
          tblInternalRate intRate = new tblInternalRate();
          intRate.BuyCCY = r.ItemArray[0].ToString();
          intRate.SellCCY = r.ItemArray[1].ToString();
          intRate.FloorRate = decimal.Parse(r.ItemArray[2].ToString());
          intRate.InternalRate = decimal.Parse(r.ItemArray[2].ToString());
          intRate.FromFileID = fileID;
          intRate.CreatedBy = sImportPeople;
          intRate.CreatedDate = importDate;
          //tblInternalRateService.CreatetblInternalRate(intRate);
          intRateCol.Add(intRate);

          if (r.ItemArray[4].ToString() != "")
          {
            intRate = new tblInternalRate();
            intRate.BuyCCY = r.ItemArray[4].ToString();
            intRate.SellCCY = r.ItemArray[5].ToString();
            intRate.FloorRate = decimal.Parse(r.ItemArray[6].ToString());
            intRate.InternalRate = decimal.Parse(r.ItemArray[6].ToString());
            intRate.FromFileID = fileID;
            intRate.CreatedBy = sImportPeople;
            intRate.CreatedDate = importDate;
            // tblInternalRateService.CreatetblInternalRate(intRate);
            intRateCol.Add(intRate);
          }
        }
      }
      return intRateCol;
    }

    private tblInternalRateCollection ReadSheetBoardParameter(DataTable tb, tblInternalRateCollection listIntRate)
    {
      tblInternalRateCollection mylistRate = new tblInternalRateCollection();


      foreach (tblInternalRate rate in listIntRate)
      {

        tblInternalRate myRate = new tblInternalRate();

        for (int i = 0; i < tb.Rows.Count; i++)//read sheet board parameter
        {
          DataRow r = tb.Rows[i];

          if (IsRateRow(r))
          {
            string buyCCY = "";
            string sellCCY = "";
            decimal ceilingTemp = 0;

            string buyCCY1 = "";
            string sellCCY1 = "";
            decimal ceilingTemp1 = 0;

            if (r.ItemArray[0].ToString() != "")
            {
              buyCCY = r.ItemArray[0].ToString();
              sellCCY = r.ItemArray[1].ToString();
              ceilingTemp = decimal.Parse(r.ItemArray[2].ToString());
            }
            if (r.ItemArray[4].ToString() != "")
            {
              buyCCY1 = r.ItemArray[4].ToString();
              sellCCY1 = r.ItemArray[5].ToString();
              ceilingTemp1 = decimal.Parse(r.ItemArray[6].ToString());
            }


            if (rate.BuyCCY.Contains(buyCCY) && rate.SellCCY.Contains(sellCCY))
            {
              myRate = FindFloorCeiling(rate, ceilingTemp);
              mylistRate.Add(myRate);
              tblInternalRateService.UpdatetblInternalRate(myRate);
              break;
            }
            else if (rate.BuyCCY.Contains(buyCCY1) && rate.SellCCY.Contains(sellCCY1))
            {
              myRate = FindFloorCeiling(rate, ceilingTemp1);
              mylistRate.Add(myRate);
              // tblInternalRateService.UpdatetblInternalRate(myRate);
              break;
            }
          }
        }
        if (myRate.CeilingRate == null)
        {
          rate.CeilingRate = -1;
          mylistRate.Add(rate);
          //  tblInternalRateService.UpdatetblInternalRate(rate);
        }
      }
      return mylistRate;
    }

    private tblInternalRate FindFloorCeiling(tblInternalRate rate, decimal rateTemp)
    {

      if (rate.FloorRate <= rateTemp)
      {
        rate.CeilingRate = rateTemp;
      }
      else if (rate.FloorRate > rateTemp)
      {
        decimal temp = rate.FloorRate.Value;
        rate.FloorRate = rateTemp;
        rate.CeilingRate = temp;
      }
      return rate;
    }

    #endregion
  }
}
